﻿//功能描述：考勤管理数据访问层
using Dapper;
using Hong.Common.Encryption;
using Hong.IRepository.IRepository_AttendanceManagement;
using Hong.Model.Dto.PesponseDto.ResponseModel.ResultModel;
using Hong.Model.SysDomain.Model_AttendanceManagement;
using Hong.Model.SysDomain.Model_AttendanceManagement.Sta;
using Hong.Repository.Data;
using Microsoft.Extensions.Caching.Distributed;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Hong.Repository.Repository.Repository_AttendanceManagement
{
    public class AttendanceRepository : IAttendanceRepository
    {
        #region 构造器注入
        private readonly T_HongShi_3Context _t_HongShi_3Context;
        private readonly DataBase _dataBase;
        private readonly DapperHelper _dapperHelper;
        private readonly ILogger<AttendanceRepository> _logger;
        private readonly IDistributedCache _cache;
        //注入解密SQL连接字符串
        private readonly SecurityStatic _securityStatic;
        public AttendanceRepository(T_HongShi_3Context t_HongShi_3Context, DataBase dataBase, ILogger<AttendanceRepository> logger, DapperHelper dapperHelper, IDistributedCache cache, SecurityStatic securityStatic)
        {
            _t_HongShi_3Context = t_HongShi_3Context;
            _dataBase = dataBase;
            _logger = logger;
            _logger.LogDebug(1, "NLog injected into HomeController");
            _dapperHelper = dapperHelper;
            _cache = cache;
            _securityStatic = securityStatic;
        }
        #endregion

        #region 添加申请
        public int AddRequest(Guid? currentStaffId, string currentStaffName, RequestInfo requestInfo)
        {
            using (IDbConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
            {
                //申请表主键
                var requestId = Guid.NewGuid();

                //申请表主键赋值
                requestInfo.RequestInfoId = requestId;

                //实例化流程实例表
                InstantiationInfo instantiationInfo = new InstantiationInfo();

                //1流程实例表主键
                var instantiationId = Guid.NewGuid();

                //1流程实例表主键赋值
                instantiationInfo.InstantiationId = instantiationId;

                //2流程实例表申请表外键赋值
                instantiationInfo.RequestInfoId = requestId;

                //3流程实例表流状态
                instantiationInfo.InstantiationState = "待审核";

                //4流程实例表发起人名
                instantiationInfo.StartStaffName = currentStaffName;

                //5流程实例表发起人ID
                instantiationInfo.StartRoleId = currentStaffId;

                //6流程实例表获取发起角色外键
                var startRoleId = conn.ExecuteScalar($" select a.RoleId from User_Role a join Personnel b on a.PersonnelId = b.PersonnelId join Roleinfo c on a.RoleId=c.RoleId where 1=1 and b.PersonnelId='{currentStaffId}' ");

                //7流程实例表获取发起角色名
                var startRoleName = conn.ExecuteScalar($" select c.RoleNmae from User_Role a join Personnel b on a.PersonnelId = b.PersonnelId join Roleinfo c on a.RoleId=c.RoleId where 1=1 and b.PersonnelId='{currentStaffId}' ");

                //8流程实例表获取当前角色外键
                var currentRoleId = conn.ExecuteScalar($" select a.RoleId from User_Role a join Personnel b on a.PersonnelId = b.PersonnelId join Roleinfo c on a.RoleId=c.RoleId where 1=1 and b.PersonnelId='{currentStaffId}' ");

                //9流程实例表获取发起角色名
                var currentRoleName = conn.ExecuteScalar($" select c.RoleNmae from User_Role a join Personnel b on a.PersonnelId = b.PersonnelId join Roleinfo c on a.RoleId=c.RoleId where 1=1 and b.PersonnelId='{currentStaffId}'");

                //10流程实例表节点外键
                var instantiationInfoNodeId = conn.ExecuteScalar($" select a.NodeId from NodeInfo a join Roleinfo b on a.StaffId=b.RoleId where b.RoleId='{currentRoleId}' ");

                //11当前操作人名
                instantiationInfo.CurrentStaffName = currentStaffName;

                //12当前操作人Id
                instantiationInfo.CurrentStaffId = currentStaffId;

                //下一节点
                var nextNode = conn.ExecuteScalar($" select a.NextNode from NodeInfo a join Roleinfo b on a.StaffId=b.RoleId where b.RoleId='{currentRoleId}' ");

                //13下一操作角色名
                var nextRoleName = conn.ExecuteScalar($"select b.RoleNmae from NodeInfo a join Roleinfo b on a.StaffId=b.RoleId where a.NodeNumber='{nextNode}'");

                //14下一操作角色ID
                var nextRoleId = conn.ExecuteScalar($"select b.RoleId from NodeInfo a join Roleinfo b on a.StaffId=b.RoleId where a.NodeNumber='{nextNode}'");

                //15操作时间
                instantiationInfo.InstanCreateTime = DateTime.Now;

                //实例化流程记录表
                RecordInfo recordInfo = new RecordInfo();

                //记录表主键
                var recordId = Guid.NewGuid();
                recordInfo.RecordId = recordId;

                //记录表流程实例外键
                recordInfo.InstantiationId = instantiationId;

                //记录表节点编号
                var nodeNumber = conn.ExecuteScalar($"select a.NodeNumber from NodeInfo a join Roleinfo b on a.StaffId=b.RoleId where b.RoleId='{currentRoleId}'");

                //记录表节点Id
                var recordInfoNodeId = conn.ExecuteScalar($"select a.NodeId from NodeInfo a join Roleinfo b on a.StaffId=b.RoleId where b.RoleId='{currentRoleId}'");

                //记录表当前操作人名
                var recordStaffName = currentStaffName;

                //记录表当前操作人ID
                var recordStaffId = currentStaffId;

                //记录表当前角色名
                var recordRoleName = conn.ExecuteScalar($"select c.RoleNmae from User_Role a join Personnel b on a.PersonnelId = b.PersonnelId join Roleinfo c on a.RoleId=c.RoleId where 1=1 and b.PersonnelId='{currentStaffId}'");

                //记录表当前角色ID
                var recordRoleId = conn.ExecuteScalar($"select c.RoleId from User_Role a join Personnel b on a.PersonnelId = b.PersonnelId join Roleinfo c on a.RoleId=c.RoleId where 1=1 and b.PersonnelId='{currentStaffId}'");

                //记录表操作时间
                recordInfo.RecordTime = DateTime.Now;

                //记录表是否已读
                recordInfo.HandleState = true;

                //记录表是否已通过
                recordInfo.PassState = true;

                conn.Open();
                //事务
                using (var begin = conn.BeginTransaction())
                {
                    try
                    {
                        //添加申请表
                        conn.Execute($"insert into RequestInfo values('{requestInfo.RequestInfoId}','{requestInfo.RequestType}','{requestInfo.Place}','{requestInfo.Reason}','{requestInfo.BeginTime}','{requestInfo.EndTime}','{requestInfo.Overtime}','{requestInfo.Driving}','{requestInfo.DrivingNum}','{requestInfo.RequestRemark}')", null, begin);
                        //添加实例表
                        conn.Execute($"insert into InstantiationInfo values('{instantiationInfo.InstantiationId}','{instantiationInfoNodeId}','{instantiationInfo.InstantiationState}','{instantiationInfo.StartStaffName}','{currentStaffId}','{startRoleName}','{startRoleId}','{instantiationInfo.CurrentStaffName}','{instantiationInfo.CurrentStaffId}','{currentRoleName}','{currentRoleId}','{nextRoleName}','{nextRoleId}','{instantiationInfo.InstanCreateTime}','{instantiationInfo.RequestInfoId}')", null, begin);
                        //添加记录表
                        conn.Execute($"insert into RecordInfo values('{recordInfo.RecordId}','{recordInfo.InstantiationId}','{nodeNumber}','{recordInfoNodeId}','{recordStaffName}','{recordStaffId}','{recordRoleName}','{recordRoleId}','{recordInfo.RecordTime}','{recordInfo.HandleState}','{recordInfo.PassState}')", null, begin);
                        //提交事务
                        begin.Commit();
                        return 1;
                    }
                    catch (Exception)
                    {
                        //回滚事务
                        begin.Rollback();
                        throw;
                    }
                }
            }
        }
        #endregion

        #region 获取请假类型
        public List<StaDictionaries> GetDictionaries()
        {
            using (IDbConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
            {
                return conn.Query<StaDictionaries>(" select dicId,dicName from Dictionaries where dicCode = 'RT_1'").ToList();
            }
        }
        #endregion

        #region 获取申请列表
        public List<ViewRequestInfo> GetRequestInfos(string type)
        {
            var sql = $"select a.RequestType,a.Reason,a.Place,a.BeginTime,a.EndTime,a.Overtime,a.RequestRemark,a.DrivingNum,c.InstantiationState,c.StartStaffName,c.StartStaffId,c.StartRoleName,c.StartRoleId,c.CurrentStaffName,c.CurrentStaffId,c.CurrentRoleName,c.CurrentRoleId,c.NextRoleName,c.NextRoleId,c.InstanCreateTime,c.RequestInfoId,c.InstantiationId,c.NodeId from RequestInfo a join InstantiationInfo c on a.RequestInfoId=c.RequestInfoId where 1=1 and a.RequestType='{type}'";
            using (IDbConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
            {
                var list = conn.Query<ViewRequestInfo>(sql).ToList();
                return list;
            }
            
            
        }
        #endregion

        #region 审批
        public int Approval(InstantiationInfo instantiationInfo)
        {
            using (IDbConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
            {
                instantiationInfo.InstanCreateTime=DateTime.Now;
                //根据当前操作人查询当前角色
                var CurrroleName = conn.ExecuteScalar($"select c.RoleNmae from User_Role a join Personnel b on a.PersonnelId = b.PersonnelId join Roleinfo c on a.RoleId=c.RoleId where 1=1 and b.PersonnelId='{instantiationInfo.CurrentStaffId}'");

                var CurrroleId = conn.ExecuteScalar($"select a.RoleId from User_Role a join Personnel b on a.PersonnelId = b.PersonnelId join Roleinfo c on a.RoleId=c.RoleId where 1=1 and b.PersonnelId='{instantiationInfo.CurrentStaffId}'");

                //根据当前角色获取下一节点
                var nextNodeId = conn.ExecuteScalar($"select a.NextNode from NodeInfo a join Roleinfo b on a.StaffId=b.RoleId where b.RoleId='{CurrroleId}'");
                //根据下一节点获取下一角色
                var nextRoleName = conn.ExecuteScalar($"select b.RoleNmae from NodeInfo a join Roleinfo b on a.StaffId=b.RoleId where a.NodeNumber='{nextNodeId}'");

                var nextRoleId = conn.ExecuteScalar($"select b.RoleId from NodeInfo a join Roleinfo b on a.StaffId=b.RoleId where a.NodeNumber='{nextNodeId}'");

                //实例化流程记录表
                RecordInfo recordInfo = new RecordInfo();

                //记录表主键
                var recordId = Guid.NewGuid();
                recordInfo.RecordId = recordId;

                //记录表流程实例外键
                recordInfo.InstantiationId = instantiationInfo.InstantiationId;

                //记录表当前角色名
                var recordRoleName = CurrroleName;

                //记录表当前角色ID
                var recordRoleId = CurrroleId;

                //记录表节点编号
                var nodeNumber = conn.ExecuteScalar($"select a.NodeNumber from NodeInfo a join Roleinfo b on a.StaffId=b.RoleId where b.RoleId='{recordRoleId}'");

                //记录表节点Id
                var recordInfoNodeId = conn.ExecuteScalar($"select a.NodeId from NodeInfo a join Roleinfo b on a.StaffId=b.RoleId where b.RoleId='{recordRoleId}'");

                //记录表当前操作人名
                var recordStaffName = instantiationInfo.CurrentStaffName;

                //记录表当前操作人ID
                var recordStaffId = instantiationInfo.CurrentStaffId;

                //记录表操作时间
                recordInfo.RecordTime = DateTime.Now;

                //记录表是否已读
                recordInfo.HandleState = true;

                //记录表是否已通过
                recordInfo.PassState = true;
                conn.Open();
                //事务
                using (var begin = conn.BeginTransaction())
                {
                    try
                    {
                        //修改实例表
                        conn.Execute($" update InstantiationInfo set NodeId='{recordInfoNodeId}',InstantiationState='{instantiationInfo.InstantiationState}',StartStaffName='{instantiationInfo.StartStaffName}',StartStaffId='{instantiationInfo.StartStaffId}',StartRoleName='{instantiationInfo.StartRoleName}',StartRoleId='{instantiationInfo.StartRoleId}',CurrentStaffName='{instantiationInfo.CurrentStaffName}',CurrentStaffId='{instantiationInfo.CurrentStaffId}',CurrentRoleName='{CurrroleName}',CurrentRoleId='{CurrroleId}',NextRoleName='{nextRoleName}',NextRoleId='{nextRoleId}',InstanCreateTime='{instantiationInfo.InstanCreateTime}',RequestInfoId='{instantiationInfo.RequestInfoId}' where InstantiationId='{instantiationInfo.InstantiationId}' ", null, begin);
                        //添加记录表
                        conn.Execute($"insert into RecordInfo values('{recordInfo.RecordId}','{recordInfo.InstantiationId}','{nodeNumber}','{recordInfoNodeId}','{recordStaffName}','{recordStaffId}','{recordRoleName}','{recordRoleId}','{recordInfo.RecordTime}','{recordInfo.HandleState}','{recordInfo.PassState}')", null, begin);
                        begin.Commit();
                        return 1;
                    }
                    catch (Exception)
                    {
                        begin.Rollback();
                        throw;
                    }
                    
                }

            }
        }
        #endregion

        #region 节假日设置

            #region 添加节假日
        public int AddHolday(HolidaysInfo holidaysInfo)
        {
            try
            {
                _t_HongShi_3Context.Add<HolidaysInfo>(holidaysInfo);
                _t_HongShi_3Context.SaveChanges();
                return 1;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex.ToString());
                throw;
            }
        }
        #endregion

            #region 显示节假日
        public List<ViewHolday> Showholday(string year)
        {
            try
            {
                var sql = "select * from HolidaysInfo where 1=1 ";
                if (!string.IsNullOrEmpty(year))
                {
                    sql += $" and Holday>='{year} - 1 - 1' and  Holday<='{year} - 12 - 31' ";
                }
                using (IDbConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
                {
                    var list = conn.Query<ViewHolday>(sql).ToList();
                    return list;
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex.ToString());
                throw;
            }
        }
        #endregion

            #region 删除节假日
        public int DelHoliday( int holidayID)
        {
            var arr = _t_HongShi_3Context.HolidaysInfos.FirstOrDefault(m => m.HoldayId == holidayID);
            _t_HongShi_3Context.HolidaysInfos.Remove(arr);
            _t_HongShi_3Context.SaveChanges();
            return 1;
        }
        #endregion

        #endregion

        #region 特殊工作日设置

            #region 添加特殊工作日
        public int AddWorkDay(WorkdayInfo workdayInfo)
        {
            try
            {
                _t_HongShi_3Context.Add<WorkdayInfo>(workdayInfo);
                _t_HongShi_3Context.SaveChanges();
                return 1;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex.ToString());
                throw;
            }
        }
        #endregion

            #region 显示特殊工作日
        public List<ViewWorkDay> ShowWorkDay(string year)
        {
            try
            {
                var sql = "select * from WorkdayInfo where 1=1 ";
                if (!string.IsNullOrEmpty(year))
                {
                    sql += $" and Workday>='{year} - 1 - 1' and  Workday<='{year} - 12 - 31' ";
                }
                using (IDbConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
                {
                    var list = conn.Query<ViewWorkDay>(sql).ToList();
                    return list;
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex.ToString());
                throw;
            }
        }
        #endregion

            #region 删除特殊工作日
        public int DelWorkDay(int workdayID)
        {
            var arr = _t_HongShi_3Context.WorkdayInfos.FirstOrDefault(m => m.WorkdayId == workdayID);
            _t_HongShi_3Context.WorkdayInfos.Remove(arr);
            _t_HongShi_3Context.SaveChanges();
            return 1;
        }
        #endregion

        #endregion

    }
}
